Triggers are store procedure, which are automatically executed or fired when some events occur like deletion, insertion of data. Triggers are database object which is attached to a table and is executed automatically. Triggers can’t be explicitly invoked. Triggers could be defined on the schema, table, view or database with which an event is associated.

Benefits of Triggers

Triggers can be benefited for the following purposes:

·        Enforcing referential integrity

·        Imposing security authorizations

·        Auditing

·        Preventing invalid transactions

·        Synchronous replication of tables

·        Generating derived field values automatically

·        Event storing and logging information on table access

CREATING TRIGGER

To add triggers in database schema CREATE TRIGGER statement is used. Triggers are database operations that are automatically executed when some events occur like deletion, insertion of data.

A trigger is fired whenever a INSERT, UPDATE or DELETE, of a particular database table occurs, or whenever an UPDATE occurs on one or more specified field of a table. Following is the syntax for creating a trigger:

Syntax:

CREATE [OR REPLACE ] TRIGGER name_of_trigger
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF name_of_column]
ON name_of_table
[REFERENCING OLD AS old NEW AS new]
[FOR EACH ROW]
WHEN (condition1) 
DECLARE
   Declaration of a code block
BEGIN
   Executable-code block
EXCEPTION
   Exception-handling-code block
END; 

CREATE OR REPLACE TRIGGER display_marks_changes
BEFORE UPDATE OR DELETE OR INSERT ON student
FOR EACH ROW
WHEN (NEW.ID > 0)DECLARE
   marks number;
BEGIN

   marks:= :NEW.marks  - :OLD.marks;

   dbms_output.put_line('Old marks: ' || :OLD. marks);

   dbms_output.put_line('New marks: ' || :NEW. marks);

   dbms_output.put_line(' marks difference: ' || marks);

END;

Trigger created

INSERT INTO STUDENT (ID, NAME, AGE, ADDRESS, marks)

VALUES (7, 'Ravi', 22, 'Chennai', 45 );